
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Node;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Patrick
 */
public class CrimeManager {

    public static ArrayList<Crime> getCrimes(Connection con, double lon, double lat, boolean worldwide) throws SQLException {
        ArrayList<Crime> list = new ArrayList();
        String sql = "SELECT description,c.id as crimeid, count(crimeid) as counter ";
        sql += " FROM crimes c left join crimereports r on c.id = r.crimeid ";
        if (!worldwide) {
            sql += " where longitude between ? and ? and latitude between ? and ? ";
        }
        sql += " group by c.id,description order by counter desc,description";
        double minLon = lon - 0.01;
        double minLat = lat - 0.01;
        double maxLon = lon + 0.01;
        double maxLat = lat + 0.01;
        PreparedStatement stat = con.prepareStatement(sql);
        if (!worldwide) {
            stat.setDouble(1, minLon);
            stat.setDouble(2, maxLon);
            stat.setDouble(3, minLat);
            stat.setDouble(4, maxLat);
        }
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            Crime c = new Crime(rs);
            list.add(c);
        }
        rs.close();
        stat.close();
        return list;
    }

    public static void reportCrime(Connection con, int crimeID, double lon, double lat) throws SQLException {
        String sql = "INSERT INTO crimereports (longitude,latitude,crimeid) values (?,?,?)";
        PreparedStatement stat = con.prepareStatement(sql);
        stat.setDouble(1, lon);
        stat.setDouble(2, lat);
        stat.setInt(3, crimeID);
        stat.execute();
        stat.close();
    }

    public static String getXMLCrimeList(Connection con, double lon, double lat, boolean worldwide) throws ParserConfigurationException, TransformerConfigurationException, SQLException, TransformerException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        ArrayList<Crime> list = getCrimes(con, lon, lat, worldwide);
        for (Crime c : list) {
            Node crime = doc.createElement("crime");
            Node description = doc.createAttribute("description");
            Node counter = doc.createAttribute("counter");
            Node crimeID = doc.createAttribute("crimeid");
            crimeID.setNodeValue(c.getCrimeID() + "");
            description.setNodeValue(c.getDescription());
            counter.setNodeValue(c.getCounter() + "");
            crime.getAttributes().setNamedItem(description);
            crime.getAttributes().setNamedItem(counter);
            crime.getAttributes().setNamedItem(crimeID);
            root.appendChild(crime);
        }
        return getXML(doc);
    }

    private static Document getBaseDocument() throws ParserConfigurationException {
        Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
        doc.appendChild(doc.createElement("crombz"));
        return doc;
    }

    private static String getXML(Document doc) throws TransformerConfigurationException, TransformerException {
        TransformerFactory tf = TransformerFactory.newInstance();
        Transformer transformer = tf.newTransformer();
        transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        StringWriter writer = new StringWriter();
        transformer.transform(new DOMSource(doc), new StreamResult(writer));
        return writer.getBuffer().toString().replaceAll("\n|\r", "");
    }
}
